package com.jiefeng.manager.common.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.text.DecimalFormat;

/**
 * 说明：Excel工具类
 * Created by WuZhiqi on 2016/5/26.
 */
public class ExcelUtil {

    /**
     * @param cell
     * @return
     * @changdonglei 工具方法
     * 将传递进来的Cell类型转换为String
     */
    public static String getStringCell(HSSFCell cell) {
        String strCell;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                /**
                 * 防止转成科学计数法
                 * 将Numeric格式化成String
                 */
                DecimalFormat df = new DecimalFormat("0");
                strCell = df.format(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("")) {
            return "";
        }
        return strCell;
    }

    /**
     * @param cell
     * @return
     * @changdonglei 工具方法
     * 将传递进来的Cell类型转换为String
     */
    public static String getStringCell(XSSFCell cell) {
        String strCell;
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                /**
                 * 防止转成科学计数法
                 * 将Numeric格式化成String
                 */
                DecimalFormat df = new DecimalFormat("0");
                strCell = df.format(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("")) {
            return "";
        }
        return strCell;
    }

    /**
     * 创建EXCEL
     *
     * @param firstSheetName    第一页名称
     * @param titles     Excel标题列表
     * @return EXcel对象
     */
    public static HSSFWorkbook createWorkbook(String firstSheetName, String[] titles){
        if(null == titles || titles.length == 0){
            return  new HSSFWorkbook();
        }
        // 产生工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //产生工作表对象,表示一页
        HSSFSheet sheet = workbook.createSheet(firstSheetName);
        //创建首行(标题行)
        HSSFRow titleRow = sheet.createRow(0);
        for (short i = 0; i < titles.length; i++) {
            HSSFCell titleCell = titleRow.createCell(i, HSSFCell.CELL_TYPE_STRING);
            titleCell.setCellValue(titles[i]);
            int dataFormat = titleCell.getCellStyle().getDataFormat();
        }
        return workbook;
    }


    /**
     * 创建EXCEL,多页
     *
     * @param sheetName    页名称
     * @param titles     Excel标题列表
     * @return EXcel对象
     */
    public static HSSFWorkbook createWorkbook4MutilSheet(String sheetName, String[] titles, int sheetNum){
        if(null == titles || titles.length == 0){
            return  new HSSFWorkbook();
        }
        // 产生工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        for(int j = 1; j <= sheetNum; j ++){
            //产生工作表对象,表示一页
            HSSFSheet sheet = workbook.createSheet(sheetName+ String.valueOf(j));
            //创建首行(标题行)
            HSSFRow titleRow = sheet.createRow(0);
            for (short i = 0; i < titles.length; i++) {
                HSSFCell titleCell = titleRow.createCell(i, HSSFCell.CELL_TYPE_STRING);
                titleCell.setCellValue(titles[i]);
                int dataFormat = titleCell.getCellStyle().getDataFormat();
            }
        }
        return workbook;
    }

}
